﻿using Dapper;
using Dapper.Contrib.Extensions;
using Publicuse.Entity;
using Publicuse.Entity.AttributeUtil;
using Publicuse.Entity.AttributeUtil.Validate;
using Publicuse.Util;
using Microsoft.Data.Sqlite;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using Z.Dapper.Plus;
using static Dapper.SqlMapper;
using StackExchange.Profiling;

namespace Publicuse.DBCore.SqlLite
{
    public class SqlLiteInstance
    {
        private string connstr;

        private static object locker = new object();

        private static SqlLiteInstance sqlLiteInstance = null;

        static SqlLiteInstance()
        {
            sqlLiteInstance = new SqlLiteInstance();
        }

        public static SqlLiteInstance GetInstance
        {
            get { return sqlLiteInstance; }
        }

        /// <summary>
        /// 数据库实例字段初始化
        /// </summary>
        /// <param name="constr"></param>
        public void Init(string constr)
        {
            if (string.IsNullOrEmpty(constr))
                return;

            this.connstr = constr;
        }

        /// <summary>
        /// 初始化数据库连接
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="msg"></param>
        /// <param name="action"></param>
        /// <returns></returns>
        private T InitConnction<T>(out string msg, Func<IDbConnection, T> action)
        {
            msg = string.Empty;
            T result = default(T);
            MiniProfiler profiler = MiniProfiler.StartNew("StartNew");
            try
            {
                using (profiler.Step("Level1"))
                {
                    SqliteConnectionStringBuilder sb = new SqliteConnectionStringBuilder();
                    sb.DataSource = $"{this.connstr}";
                    DbConnection connection = new SqliteConnection(sb.ToString());
                    using (IDbConnection con = new SqliteConnection(sb.ToString()))
                    {
                        if (MiniProfiler.Current != null)
                        {
                            connection = new StackExchange.Profiling.Data.ProfiledDbConnection(connection, MiniProfiler.Current);
                        }
                        connection.Open();
                        con.Open();
                        result = action.Invoke(con);
                        connection.Close();
                        con.Close();
                    }
                }
                WriteLog(profiler);
                return result;
            }
            catch (Exception ex)
            {
                //LogUtil.GetInstance.WriteLogOld("数据库连接异常", ex.Message + "|" + this.connstr, true);
                msg = ex.Message;
                return default(T);
            }
        }

        /// <summary>
        /// 新增信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public long Insert<T>(T entity, out string msg) where T : class, new()
        {
            long result = -1;
            msg = string.Empty;
            var bkd = this.InitConnction(out msg, con =>
            {
                if (!entity.ValidateModel(out var msg))
                {
                    throw new Exception(msg);
                }
                var tran = con.BeginTransaction();
                result = con.Insert<T>(entity, tran);
                if (result > -1)
                {
                    tran.Commit();
                }
                else
                {
                    tran.Rollback();
                }
                return result;
            });
            return result;
        }

        /// <summary>
        /// 批量新增信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ts"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public IEnumerable<T> Insert<T>(List<T> ts, out string msg)
        {
            var result = this.InitConnction(out msg, con =>
            {
                foreach (var entity in ts)
                {
                    if (!entity.ValidateModel(out var msg))
                    {
                        throw new Exception(msg);
                    }
                }
          
                DapperPlusActionSet<T> result = con.BulkInsert<T>(ts);
                return result.Current;
            });
            return result;
        }

        /// <summary>
        /// 修改信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Update<T>(T entity, out string msg) where T : class, new()
        {
            bool result = true;
            msg = string.Empty;
            result = this.InitConnction(out msg, con =>
            {
                if (!entity.ValidateModel(out var msg))
                {
                    throw new Exception(msg);
                }
                var tran = con.BeginTransaction();
                result = con.Update<T>(entity, tran);
                if (result)
                {
                    tran.Commit();
                }
                else
                {
                    tran.Rollback();
                }
                return result;
            });
            return result;
        }

        /// <summary>
        /// 批量修改信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ts"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T Update<T>(IEnumerable<T> ts, out string msg) where T : class, new()
        {
            msg = string.Empty;
            var result = this.InitConnction(out msg, con =>
            {
                foreach (var entity in ts)
                {
                    if (!entity.ValidateModel(out var msg))
                    {
                        throw new Exception(msg);
                    }
                }
                DapperPlusActionSet<T> result = con.BulkUpdate<T>(ts);
                return result.CurrentItem;
            });
            return result;
        }

        /// <summary>
        /// 批量删除信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(List<string> ids, out string msg)
        {
            bool result = true;
            msg = string.Empty;
            this.InitConnction(out msg, con =>
            {
                var table = TableAtributeUtil.GetTable<T>();
                var dids = String.Join(",", ids);
                var sql = $"select * from {table} where Id in({dids})";
                var data = con.Query<T>(sql); ;
                con.BulkDelete(data);
                return true;
            });
            return result;
        }

        /// <summary>
        /// 根据主键删除信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool Delete<T>(long id, out string msg) where T : class, new()
        {
            var result = true;
            msg = string.Empty;
            result = this.InitConnction(out msg, con =>
            {
                var entity = con.Get<T>(id);
                result = con.Delete<T>(entity);
                return result;
            });
            return result;
        }

        /// <summary>
        /// 根据SQL获取第一条信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public T QueryFirst<T>(string sql, out string msg) where T : class, new()
        {
            msg = string.Empty;
            T entity = default(T);
            this.InitConnction(out msg, con =>
            {
                entity = con.QueryFirst<T>(sql);
                return entity;
            });
            return entity;
        }

        /// <summary>
        /// 根据SQL条件查询信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public List<T> Query<T>(string sql, object param, out string msg)
        {
            msg = string.Empty;
            return this.InitConnction(out msg, con =>
            {
                var result = con.Query<T>(sql, param).ToList();
                return result;
            });
        }

        /// <summary>
        /// 根据条件执行SQL 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool ExecuteSql(string sql, object param, out string msg)
        {
            msg = string.Empty;
            return this.InitConnction(out msg, con =>
            {
                return con.Execute(sql, param) > -1;
            });
        }

        /// <summary>
        /// 根据SQL获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="msg"></param>
        /// <param name="list"></param>
        public List<T> Query<T>(string sql, out string msg) where T : class, new()
        {
            msg = string.Empty;
            var list = new List<T>();
            list = this.InitConnction(out msg, con =>
            {
                var list = con.Query<T>(sql).ToList();
                return list;
            });
            return list;
        }

        public List<T> GetAll<T>(out string msg) where T : class, new()
        {
            msg = string.Empty;
            var list = new List<T>();
            list = this.InitConnction(out msg, con =>
            {
                var list = con.GetAll<T>().ToList();
                return list;
            });
            return list;
        }

        /// <summary>
        /// 分页查询列表信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tablename"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="sqlWhere"></param>
        /// <param name="sortFile"></param>
        /// <param name="srotType"></param>
        /// <param name="count"></param>
        /// <param name="exception"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public bool GetList<T>(string storedprocedure, string tablename, string queryFile, int pageIndex, int pageSize, string sqlWhere,
            string sortFile, string srotType, out long count, out string exception, out List<T> list) where T : class, new()
        {
            list = new List<T>();
            count = 0;
            long total = 0;
            exception = string.Empty;
            list = InitConnction(out exception, con =>
            {
                var param = new DynamicParameters();
                List<T> list = new List<T>();
                //param.Add("p_table_name", tablename, DbType.String, ParameterDirection.Input);
                //param.Add("p_files", queryFile, DbType.String, ParameterDirection.Input);
                //param.Add("p_curr_page", pageIndex, DbType.Int32, ParameterDirection.Input);
                //param.Add("p_page_size", pageSize, DbType.Int32, ParameterDirection.Input);
                //param.Add("p_order_string", "order by " + sortFile, DbType.String, ParameterDirection.Input);
                //param.Add("p_ordertype_string", string.IsNullOrEmpty(srotType) ? "desc" : srotType, DbType.String, ParameterDirection.Input);
                //param.Add("p_where_string", sqlWhere, DbType.String, ParameterDirection.Input);
                //param.Add("p_total_rows", 0, DbType.Int32, ParameterDirection.Output);
                //list = con.Query<T>(storedprocedure, param, commandType: CommandType.StoredProcedure).ToList();
                //total = param.Get<int>("p_total_rows");

                var startrow = (pageIndex - 1) * pageSize;
                var sql = $"select count(*) from {tablename} {sqlWhere};";
                total = (long)con.ExecuteScalar(sql);
                if (string.IsNullOrEmpty(srotType))
                {
                    srotType = "desc";
                }
                var limitstr = $"limit {startrow},{pageSize} ";
                sql = $"select {queryFile} from {tablename} {sqlWhere} order by {sortFile} {srotType} {limitstr} ";
                list = con.Query<T>(sql).ToList();
                return list;
            });
            count = total;
            return string.IsNullOrEmpty(exception) ? true : false;
        }

        /// <summary>
        /// 通用CUD操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="cUDType"></param>
        /// <param name="exception"></param>
        /// <param name="sql"></param>
        /// <returns>返回操作结果</returns>
        public int CUDEntity<T>(T entity, CUDType cUDType, out string exception, string sql = null) where T : new()
        {
            exception = null;
            var sqlParameter = new DynamicParameters();
            CURDModel cURDModel = null;
            if (string.IsNullOrEmpty(sql))
            {
                switch (cUDType)
                {
                    case CUDType.Add:
                        {
                            sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteInser, entity, out exception, out sql).GetDynamicParameters;
                        }
                        break;
                    case CUDType.Update:
                        {
                            sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteUpdate, entity, out exception, out sql).GetDynamicParameters;
                        }
                        break;
                    case CUDType.Delete:
                        {
                            sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteDelete, entity, out exception, out sql).GetDynamicParameters;
                        }
                        break;
                }
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Params, cUDType = cUDType };
            }
            else
            {
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Default, cUDType = cUDType };
            }
            return Execute(sql, sqlParameter, cURDModel, false, out exception);
        }

        /// <summary>
        /// 批量新增修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="cUDType"></param>
        /// <param name="exception"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        public List<int> CUDEntity<T>(List<T> entitys, CUDType cUDType, out string exception, string sql = null) where T : new()
        {
            exception = null;
            var sqlParameter = new DynamicParameters();
            CURDModel cURDModel = null;
            List<DynamicParameters> listsqlParameter = new List<DynamicParameters>();
            List<string> listsql = new List<string>();
            if (string.IsNullOrEmpty(sql))
            {
                foreach (var entity in entitys)
                {
                    switch (cUDType)
                    {
                        case CUDType.Add:
                            {
                                sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteInser, entity, out exception, out sql).GetDynamicParameters;
                            }
                            break;
                        case CUDType.Update:
                            {
                                sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteUpdate, entity, out exception, out sql).GetDynamicParameters;
                            }
                            break;
                        case CUDType.Delete:
                            {
                                sqlParameter = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteDelete, entity, out exception, out sql).GetDynamicParameters;
                            }
                            break;
                    }
                    listsqlParameter.Add(sqlParameter);
                    listsql.Add(sql);
                }
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Params, cUDType = cUDType };
            }
            else
            {
                cURDModel = new CURDModel() { sqlParamsType = SqlParamsType.Default, cUDType = cUDType };
            }
            return Execute(listsql, listsqlParameter, cURDModel, false, out exception);
        }

        /// <summary>
        /// 根据主键获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="Id"></param>
        /// <param name="exception"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public bool GetEntityById<T>(long Id, out string exception, out T t) where T : new()
        {
            t = new T();
            exception = null;
            var msg = string.Empty;
            t = InitConnction(out exception, con =>
            {
                var t = new T();
                Type type = typeof(T);
                var sqlParam = SqlCacheBuilder<T>.GSqlCacheBuilder.GetSql(SqlCacheBuildertType.SqlLiteQuery, default(T), out msg, out var sql, Id);
                var data = con.Query<T>(sql, new { Id = Id });
                if (data.Count() > 0)
                {
                    t = data.FirstOrDefault();
                }
                return t;
            });
            return string.IsNullOrEmpty(msg) ? true : false;
        }

        /// <summary>
        /// 根据条件获取信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="msg"></param>
        /// <param name="t"></param>
        public void QueryInfoBySql<T>(string sql, List<Tuple<string, string, ExcuteType>> tuples, out string exception, out T t) where T : class, new()
        {
            t = new T();
            exception = null;
            var msg = string.Empty;
            t = InitConnction(out exception, con =>
            {
                var t = new T();
                Type type = typeof(T);
                var dynamicParameters = new DynamicParameters();
                InitParam(ref sql, tuples, ref dynamicParameters);
                var data = con.Query<T>(sql, dynamicParameters);
                if (data.Count() > 0)
                {
                    t = data.FirstOrDefault();
                }
                return t;
            });
        }

        /// <summary>
        /// 根据SQL获取DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, object param, out string msg)
        {
            msg = string.Empty;
            return this.InitConnction(out msg, con =>
            {
                var result = con.ExecuteReader(sql, param);
                DataTable dataTable = new DataTable();
                dataTable.Load(result);
                return dataTable;
            });
        }

        /// <summary>
        /// DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="exception"></param>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public bool GetDataSet(string sql, out string exception, out DataSet dataSet)
        {
            dataSet = null;
            exception = null;
            var msg = string.Empty;
            var isSuccess = true;
            dataSet = InitConnction(out exception, con =>
            {
                DataSet ds = new DataSet();
                var data = con.ExecuteReader(sql);
                ds = DataTableUtil.ConvertDataReaderToDataSet(data);
                return ds;
            });
            if (!string.IsNullOrEmpty(msg))
            {
                exception += msg;
                isSuccess = false;
            }
            return isSuccess;
        }

        /// <summary>
        /// 根据SQL获取DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql, object param, out string exception)
        {
            exception = null;
            var msg = string.Empty;
            return InitConnction(out exception, con =>
            {
                DataSet ds = new DataSet();
                var data = con.ExecuteReader(sql, param);
                ds = DataTableUtil.ConvertDataReaderToDataSet(data);
                return ds;
            });
        }

        /// <summary>
        /// 批量操作
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="cURDModel"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public List<int> Execute(List<string> sqlstr, List<DynamicParameters> param, CURDModel cURDModel, bool isproc, out string exception)
        {
            exception = null;
            List<int> result = new List<int>();
            IDbTransaction tran = null;

            result = InitConnction(out exception, (con) =>
            {
                using (tran = con.BeginTransaction())
                {
                    var index = 0;
                    foreach (var sql in sqlstr)
                    {
                        con.Execute(sql, param[index]);
                        var res = cURDModel.cUDType == CUDType.Add ? (int)con.ExecuteScalar(sql, param[index]) : con.Execute(sql, param[index]);
                        if (res < 0)
                        {
                            if (tran != null)
                                tran.Rollback();

                            result.Add(-1);
                        }
                        else
                        {
                            result.Add(res);
                        }
                        index++;
                    }
                    tran.Commit();
                    return result;
                }
            });
            return result;
        }

        /// <summary>
        /// 相当于把对数据库的命令全部记录下来，中间也有Sql语句部分；
        /// </summary>
        /// <param name="profiler"></param>
        private static void WriteLog(MiniProfiler profiler)
        {
            if (profiler?.Root != null)
            {
                var root = profiler.Root;
                if (root.HasChildren)
                {
                    root.Children.ForEach(chil =>
                    {
                        if (chil.CustomTimings?.Count > 0)
                        {
                            foreach (var customTiming in chil.CustomTimings)
                            {
                                var all_sql = new List<string>();
                                var err_sql = new List<string>();
                                var all_log = new List<string>();
                                int i = 1;
                                customTiming.Value?.ForEach(value =>
                                {
                                    if (value.ExecuteType != "OpenAsync")
                                        all_sql.Add(value.CommandString);
                                    if (value.Errored)
                                        err_sql.Add(value.CommandString);
                                    var log = $@"【{customTiming.Key}{i++}】{value.CommandString} Execute time :{value.DurationMilliseconds} ms,Start offset :{value.StartMilliseconds} ms,Errored :{value.Errored}";
                                    all_log.Add(log);
                                });
                               // LogUtil.GetInstance.WriteLogOld("Dapper执行SQL", Newtonsoft.Json.JsonConvert.SerializeObject(all_log), true);
                            }
                        }
                    });
                }
            }
        }

        /// <summary>
        /// 就是通过json字符串找出更新的字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="json"></param>
        /// <param name="id"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public int Update<T>(string json, int id, out string msg)
        {
            msg = string.Empty;
            try
            {
                var result = this.InitConnction(out msg, con =>
                {
                    Type type = typeof(T);
                    T t = JsonUtil.ToObject<T>(json);
                    string stringSet = string.Join(",", type.GetPropertiesInJson(json).Select(p => $"{p.GetMappingName()}=@{p.Name}"));
                    var keyId = TableAtributeUtil.GetPrimaryKey<T>();
                    string sql = $"update {type.GetMappingName()} set {stringSet} where {keyId}=@{keyId};";
                    var dynamicParameters = new DynamicParameters();
                    foreach (var item in type.GetPropertiesInJson(json))
                    {
                        dynamicParameters.Add(item.GetMappingName(), item.GetValue(t));
                    }
                    dynamicParameters.Add(keyId, id);
                    using (var tran = con.BeginTransaction())
                    {
                        var res = con.Execute(sql, dynamicParameters, tran);
                        if (res < 0)
                        {
                            if (tran != null)
                                tran.Rollback();
                        }
                        else
                        {
                            tran.Commit();
                        }
                        return res;
                    }
                });
                return result;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
                return 0;
            }
        }

        /// <summary>
        /// ExecuteNonQuery
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <param name="param"></param>
        /// <param name="cURDModel"></param>
        /// <param name="isproc"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        public int Execute(string sqlstr, DynamicParameters param, CURDModel cURDModel, bool isproc, out string exception)
        {
            exception = null;
            int result = -1;
            var msg = string.Empty;
            result = InitConnction(out exception, con =>
            {
                int result = -1;
                using (var tran = con.BeginTransaction())
                {
                    result = cURDModel.cUDType == CUDType.Add ? Convert.ToInt32(con.ExecuteScalar(sqlstr, param, tran)) : con.Execute(sqlstr, param, tran);
                    if (result < 0)
                    {
                        if (tran != null)
                            tran.Rollback();
                    }
                    else
                    {
                        tran.Commit();
                    }
                }
                return result;
            });
            return result;
        }
        public void QueryMultiple(string sql, object param, out string msg, Action<GridReader> action)
        {
            this.InitConnction(out msg, con =>
            {
                var multi = con.QueryMultiple(sql, param);
                action?.Invoke(multi);
                return true;
            });
        }

        public void Query(string sql, object param, out string msg, Action<IDbConnection> action)
        {
            this.InitConnction(out msg, con =>
            {
                action?.Invoke(con);
                return true;
            });
        }

        /// <summary>
        /// 初始化参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tuples"></param>
        /// <param name="parameters"></param>
        private static void InitParam(ref string sql, List<Tuple<string, string, ExcuteType>> tuples, ref DynamicParameters parameters)
        {
            var index = 0;
            DynamicParameters dynamicParameters = new DynamicParameters();
            if (tuples != null && tuples.Count > 0)
            {
                string newsql = sql;
                tuples.ForEach(a =>
                {
                    if (index == 0)
                    {
                        if (a.Item3 == ExcuteType.Equal)
                        {
                            newsql += $" where {a.Item1}=@{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.In)
                        {
                            newsql += $" where {a.Item1} in @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.NoEqual)
                        {
                            newsql += $" where {a.Item1} <> @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.gte)
                        {
                            newsql += $" where {a.Item1} >= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.lte)
                        {
                            newsql += $" where {a.Item1} <= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.findinset)
                        {
                            newsql += $" where find_in_set({a.Item2},{a.Item1}) ";
                        }
                    }
                    else
                    {
                        if (a.Item3 == ExcuteType.Equal)
                        {
                            newsql += $" and {a.Item1}=@{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.In)
                        {
                            newsql += $" and {a.Item1} in @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.NoEqual)
                        {
                            newsql += $" and {a.Item1} <> @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.gte)
                        {
                            newsql += $" and {a.Item1} >= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.lte)
                        {
                            newsql += $" and {a.Item1} <= @{a.Item1} ";
                        }
                        else if (a.Item3 == ExcuteType.findinset)
                        {
                            newsql += $" and find_in_set({a.Item2},{a.Item1}) ";
                        }
                    }
                    if (a.Item3 == ExcuteType.In)
                    {
                        dynamicParameters.Add(a.Item1, a.Item2.Split(','));
                    }
                    else
                    {
                        dynamicParameters.Add(a.Item1, a.Item2);
                    }
                    index++;
                });
                sql = newsql;
                parameters = dynamicParameters;
            }
        }

    }
}
